package com.es.hr.empQuit;

import java.lang.reflect.Field;
import java.util.ArrayList;

import com.es.hr.emp.EmpInfoBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class EmpQuitBiz {

	public PageBean queryEmpList(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp.emp_id,emp.emp_area_value,ltrim(rtrim(emp.emp_login_yn)) emp_login_yn,emp.emp_province_value,emp.emp_city_value,emp.emp_name,emp.emp_login_id,(select top 1 common_value from hrm_common "
					+ "where common_code = emp.emp_role and common_type='role') emp_role," 
					+ "(select top 1 a.emp_name from hrm_emp_info a where a.emp_ctm_code = emp.emp_id) ctm_name,"
					+ "emp.emp_code,emp.emp_job,emp.emp_phone_mobile,emp.emp_phone_native,emp.emp_mail,emp.shop_name," 
					+" emp.emp_city_level,emp.emp_shiyebu,emp.emp_rtm_name,emp.emp_level,emp.itmall_city,emp.itmall_city_num,emp.c3_city,emp.c3_city_num  "
					+ " from hrm_emp_info emp where emp.del_flag='N' and (emp.emp_lizhi_status is null or emp.emp_lizhi_status='N')";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and emp.emp_shiyebu = ?",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_job) like '%'+UPPER(?)+'%'",
					" and emp.emp_rtm_code=?",
					" and (emp.emp_id=?  or  emp.emp_role not in(?,?,?,?,?))"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")&&i<9) {
						sql += sqlArrary[i];
						query.add(param.get(i).trim());
				}
			}
			if(param.size()>9){
				sql += sqlArrary[9];
				query.add(param.get(9).trim());
				
				query.add(param.get(10).trim());
				query.add(param.get(11).trim());
				query.add(param.get(12).trim());
				query.add(param.get(13).trim());
				query.add(param.get(14).trim());
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
		}
	
	public PageBean retrieveEmpQuitListHistory(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select convert(varchar, emp_lizhi_date, 111)  emp_lizhi_date,emp_lizhi_yuanyin, emp.emp_id,emp.emp_area_value,ltrim(rtrim(emp.emp_login_yn)) emp_login_yn,emp.emp_province_value,emp.emp_city_value,emp.emp_name,emp.emp_login_id,(select top 1 common_value from hrm_common "
					+ "where common_code = emp.emp_role and common_type='role') emp_role," 
					+ "(select top 1 a.emp_name from hrm_emp_info a where a.emp_ctm_code = emp.emp_id) ctm_name,"
					+ "emp.emp_code,emp.emp_job,emp.emp_phone_mobile,emp.emp_phone_native,emp.emp_mail,emp.shop_name," 
					+" emp.emp_city_level,emp.emp_shiyebu,emp.emp_rtm_name,emp.emp_level,emp.itmall_city,emp.itmall_city_num,emp.c3_city,emp.c3_city_num  "
					+ " from hrm_emp_info emp where emp.emp_lizhi_status = 'Y'";
			String[] sqlArrary = new String[] { "and emp.emp_area_code=? ",
					"and emp.emp_province_code=? ", "and emp.emp_city_code=? ",
					"and emp.emp_shiyebu = ?",
					"and emp.emp_role=? ",
					"and UPPER(emp.emp_name) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_code) like '%'+UPPER(?)+'%'",
					"and UPPER(emp.emp_job) like '%'+UPPER(?)+'%'",
					" and emp.emp_rtm_code=?",
					" and (emp.emp_id=?  or  emp.emp_role not in(?,?,?,?,?))"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")&&i<9) {
						sql += sqlArrary[i];
						query.add(param.get(i).trim());
				}
			}
			if(param.size()>9){
				sql += sqlArrary[9];
				query.add(param.get(9).trim());
				
				query.add(param.get(10).trim());
				query.add(param.get(11).trim());
				query.add(param.get(12).trim());
				query.add(param.get(13).trim());
				query.add(param.get(14).trim());
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
		}
	
	
	@SuppressWarnings("unchecked")
	public boolean empQuitDo(String emp_id,String lizhi_result) throws Exception{
		Dao dao = new Dao();
		ArrayList query = new ArrayList();
		query.add(lizhi_result);
		query.add(emp_id);
		
		try {
			String sql = "update hrm_emp_info set del_flag='Y',emp_lizhidan_yn='Y',emp_lizhi_status = 'Y',emp_lizhi_date=GETDATE(),emp_lizhi_yuanyin=? where emp_id=?";
			dao.update(sql,query);
		} catch(Exception e){
				System.out.print(e.getMessage());
			   return false;
		   }finally {		   
			   dao.close();
		    }
		    return true;
	}
}
